set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


with dwd_route_city_fast as (
    select
         fast.in_from_code                    as in_from_code          --始发网点编码
        ,fast.in_from_name                    as in_from_name          --始发网点名称
        ,fast.in_from_city_code               as in_from_city_code     --始发城市编码
        ,fast.in_from_city_desc               as in_from_city_desc     --始发城市名称
        ,fast.in_from_financial_center_code   as in_from_agent_code    --始发代理区编码
        ,fast.in_from_financial_center_desc   as in_from_agent_desc    --始发代理区名称
        ,fast.out_to_code                     as out_to_code           --目的网点编码
        ,fast.out_to_name                     as out_to_name           --目的网点名称
        ,fast.out_to_city_code                as out_to_city_code      --目的城市编码
        ,fast.out_to_city_desc                as out_to_city_desc      --目的城市名称
        ,fast.out_to_financial_center_code    as out_to_agent_code     --目的代理区编码
        ,fast.out_to_financial_center_desc    as out_to_agent_desc     --目的代理区名称
        ,fast.in_from_regional_code           as in_from_regional_code --始发大区编码
        ,fast.in_from_regional_desc           as in_from_regional_desc --始发大区名称
        ,fast.out_to_regional_code            as out_to_regional_code  --目的大区编码
        ,fast.out_to_regional_desc            as out_to_regional_desc  --目的大区名称
        ,fast.effective_times_cn              as effective_times_cn    --菜鸟时效
        ,dim.economic_name                    as economic_name         --经济圈
        ,config_start.area_code               as start_area_code
        ,config_start.area_name               as start_area_name
        ,config_start.new_agent_code          as start_new_agent_code
        ,config_start.new_agent_name          as start_new_agent_name
        ,config_start.region_code             as start_region_code
        ,config_start.region_name             as start_region_name
        ,config_end.area_code                 as end_area_code
        ,config_end.area_name                 as end_area_name
        ,config_end.new_agent_code            as end_new_agent_code
        ,config_end.new_agent_name            as end_new_agent_name
        ,config_end.region_code               as end_region_code
        ,config_end.region_name               as end_region_name
        ,fast.transfer_type                   as transfer_type 
        ,fast.search_type                     as search_type
        ,sum(cn_cp_line_cnt) as equal_line_cnt     --持平线路数
        ,sum(cn_my_line_cnt) as slow_line_cnt      --慢于线路数
        ,sum(all_line_cnt  ) as all_line_cnt       --总线路数
        ,sum(cn_cp_cnt     ) as equal_cnt          --持平件量
        ,sum(cn_my_cnt     ) as slow_cnt           --慢于件量
        ,sum(all_cnt       ) as all_cnt            --总件量
        ,dt as dt
    from jms_dm.dm_route_city_effect_base_dt fast
    left join jms_dim.dim_enum_economic_circle_base dim
      on fast.in_from_city_desc = dim.start_city_name  --始发城市
     and fast.out_to_city_desc = dim.end_city_name     --目的城市
    left join jms_dim.dim_tms_city_prescription_config_base config_start
      on fast.in_from_financial_center_code = config_start.agent_code --始发代理区
    left join jms_dim.dim_tms_city_prescription_config_base config_end
      on fast.out_to_financial_center_code = config_end.agent_code    --目的代理区
    where fast.dt = '{{ execution_date | cst_ds }}'
    group by fast.in_from_code                  
            ,fast.in_from_name                  
            ,fast.in_from_city_code             
            ,fast.in_from_city_desc             
            ,fast.in_from_financial_center_code 
            ,fast.in_from_financial_center_desc 
            ,fast.out_to_code                   
            ,fast.out_to_name                   
            ,fast.out_to_city_code              
            ,fast.out_to_city_desc              
            ,fast.out_to_financial_center_code  
            ,fast.out_to_financial_center_desc  
            ,fast.in_from_regional_code         
            ,fast.in_from_regional_desc         
            ,fast.out_to_regional_code          
            ,fast.out_to_regional_desc          
            ,fast.effective_times_cn    
            ,fast.transfer_type        
            ,fast.search_type     
            ,dim.economic_name                  
            ,config_start.area_code      
            ,config_start.area_name      
            ,config_start.new_agent_code 
            ,config_start.new_agent_name 
            ,config_start.region_code    
            ,config_start.region_name    
            ,config_end.area_code        
            ,config_end.area_name        
            ,config_end.new_agent_code   
            ,config_end.new_agent_name   
            ,config_end.region_code      
            ,config_end.region_name   
            ,dt 
)


insert overwrite table jms_tmp.dm_route_city_summary_dt_tmp
select
     in_from_agent_code           as in_from_agent_code       --始发代理区编码
    ,in_from_agent_desc           as in_from_agent_desc       --始发代理区名称
    ,in_from_new_agent_code       as in_from_new_agent_code   --新始发代理区编码
    ,in_from_new_agent_desc       as in_from_new_agent_desc   --新始发代理区名称
    ,in_from_regional_code        as in_from_regional_code    --始发大区编码
    ,in_from_regional_desc        as in_from_regional_desc    --始发大区名称
    ,out_to_agent_code            as out_to_agent_code        --目的代理区编码
    ,out_to_agent_desc            as out_to_agent_desc        --目的代理区名称
    ,out_to_new_agent_code        as out_to_new_agent_code    --新目的代理区编码
    ,out_to_new_agent_desc        as out_to_new_agent_desc    --新目的代理区名称
    ,out_to_regional_code         as out_to_regional_code     --目的大区编码
    ,out_to_regional_desc         as out_to_regional_desc     --目的大区名称
    ,economic_name                as economic_name            --经济圈名称
    ,city_cnt                     as city_cnt                 --代理区城市流向线路数量
    ,city_equal_cnt               as city_equal_cnt           --代理区城市流向持平线路数量
    ,equal_cnt                    as equal_cnt                --持平件量
    ,slow_cnt                     as slow_cnt                 --慢于件量
    ,direct_cnt                   as direct_cnt               --直发件量
    ,trans_cnt                    as trans_cnt                --中转件量
    ,all_cnt                      as all_cnt                  --总件量
    ,equal_line_cnt               as equal_line_cnt           --持平线路数
    ,slow_line_cnt                as slow_line_cnt            --慢于线路数
    ,direct_line_cnt              as direct_line_cnt          --直发线路数
    ,trans_line_cnt               as trans_line_cnt           --中转线路数
    ,all_line_cnt                 as all_line_cnt             --总线路数
    ,sum(city_cnt)         over() as all_city_cnt             --全国流向
    ,sum(city_equal_cnt)   over() as all_city_equal_cnt       --全国持平流向
    ,sum(equal_cnt)        over() as all_equal_piect_cnt      --全国持平件量
    ,sum(slow_cnt)         over() as all_slow_piect_cnt       --全国慢于件量
    ,sum(direct_cnt)       over() as all_direct_piect_cnt     --全国直发件量
    ,sum(trans_cnt)        over() as all_trans_piect_cnt      --全国中转件量
    ,sum(all_cnt)          over() as all_piect_cnt            --全国件量
    ,sum(equal_line_cnt )  over() as all_equal_line_cnt       --全国持平线路数
    ,sum(slow_line_cnt  )  over() as all_slow_line_cnt        --全国慢于线路数
    ,sum(direct_line_cnt)  over() as all_direct_line_cnt      --全国直发线路数
    ,sum(trans_line_cnt )  over() as all_trans_line_cnt       --全国中转线路数
    ,sum(all_line_cnt)     over() as all_any_line_cnt         --全国总线路数
    ,dt
from(
    select
         in_from_agent_code           as in_from_agent_code       --始发代理区编码
        ,in_from_agent_desc           as in_from_agent_desc       --始发代理区名称
        ,in_from_new_agent_code       as in_from_new_agent_code   --新始发代理区编码
        ,in_from_new_agent_desc       as in_from_new_agent_desc   --新始发代理区名称
        ,in_from_regional_code        as in_from_regional_code    --始发大区编码
        ,in_from_regional_desc        as in_from_regional_desc    --始发大区名称
        ,out_to_agent_code            as out_to_agent_code        --目的代理区编码
        ,out_to_agent_desc            as out_to_agent_desc        --目的代理区名称
        ,out_to_new_agent_code        as out_to_new_agent_code    --新目的代理区编码
        ,out_to_new_agent_desc        as out_to_new_agent_desc    --新目的代理区名称
        ,out_to_regional_code         as out_to_regional_code     --目的大区编码
        ,out_to_regional_desc         as out_to_regional_desc     --目的大区名称
        ,economic_name                as economic_name            --经济圈名称
        ,nvl(max(city_cnt       ),0)  as city_cnt                 --代理区城市流向线路数量
        ,nvl(max(city_equal_cnt ),0)  as city_equal_cnt           --代理区城市流向持平线路数量
        ,nvl(max(equal_cnt      ),0)  as equal_cnt                --持平件量
        ,nvl(max(slow_cnt       ),0)  as slow_cnt                 --慢于件量
        ,nvl(max(direct_cnt     ),0)  as direct_cnt               --直发件量
        ,nvl(max(trans_cnt      ),0)  as trans_cnt                --中转件量
        ,nvl(max(all_cnt        ),0)  as all_cnt                  --总件量
        ,nvl(max(equal_line_cnt ),0)  as equal_line_cnt           --持平线路数
        ,nvl(max(slow_line_cnt  ),0)  as slow_line_cnt            --慢于线路数
        ,nvl(max(direct_line_cnt),0)  as direct_line_cnt          --直发线路数
        ,nvl(max(trans_line_cnt ),0)  as trans_line_cnt           --中转线路数
        ,nvl(max(all_line_cnt   ),0)  as all_line_cnt             --总线路数
        ,dt                           as dt                       --分区日期
    from (
        select
             route_city.in_from_agent_code      as in_from_agent_code     --始发代理区编码
            ,route_city.in_from_agent_desc      as in_from_agent_desc     --始发代理区名称
            ,route_city.in_from_new_agent_code  as in_from_new_agent_code --新始发代理区编码
            ,route_city.in_from_new_agent_desc  as in_from_new_agent_desc --新始发代理区名称
            ,route_city.in_from_regional_code   as in_from_regional_code  --始发大区编码
            ,route_city.in_from_regional_desc   as in_from_regional_desc  --始发大区名称
            ,route_city.out_to_agent_code       as out_to_agent_code      --目的代理区编码
            ,route_city.out_to_agent_desc       as out_to_agent_desc      --目的代理区名称
            ,route_city.out_to_new_agent_code   as out_to_new_agent_code  --新目的代理区编码
            ,route_city.out_to_new_agent_desc   as out_to_new_agent_desc  --新目的代理区名称
            ,route_city.out_to_regional_code    as out_to_regional_code   --目的大区编码
            ,route_city.out_to_regional_desc    as out_to_regional_desc   --目的大区名称
            ,route_city.economic_name           as economic_name          --经济圈名称
            ,nvl(route_city.city_cnt       ,0)  as city_cnt               --代理区城市流向线路数量
            ,nvl(route_city.city_equal_cnt ,0)  as city_equal_cnt         --代理区城市流向持平线路数量
            ,0                                  as equal_line_cnt         --持平线路数
            ,0                                  as slow_line_cnt          --慢于线路数
            ,0                                  as direct_line_cnt        --直发线路数
            ,0                                  as trans_line_cnt         --中转线路数
            ,0                                  as all_line_cnt           --总线路数
            ,0                                  as equal_cnt              --持平件量
            ,0                                  as slow_cnt               --慢于件量
            ,0                                  as direct_cnt             --直发件量
            ,0                                  as trans_cnt              --中转件量
            ,0                                  as all_cnt                --总件量
            ,route_city.dt                      as dt                     --分区日期
        from (
            select
                 in_from_agent_code       as in_from_agent_code     --始发代理区编码
                ,in_from_agent_desc       as in_from_agent_desc     --始发代理区名称
                ,in_from_new_agent_code   as in_from_new_agent_code --新始发代理区编码
                ,in_from_new_agent_desc   as in_from_new_agent_desc --新始发代理区名称
                ,in_from_regional_code    as in_from_regional_code  --始发大区编码
                ,in_from_regional_desc    as in_from_regional_desc  --始发大区名称
                ,out_to_agent_code        as out_to_agent_code      --目的代理区编码
                ,out_to_agent_desc        as out_to_agent_desc      --目的代理区名称
                ,out_to_new_agent_code    as out_to_new_agent_code  --新目的代理区编码
                ,out_to_new_agent_desc    as out_to_new_agent_desc  --新目的代理区名称
                ,out_to_regional_code     as out_to_regional_code   --目的大区编码
                ,out_to_regional_desc     as out_to_regional_desc   --目的大区名称
                ,economic_name            as economic_name          --经济圈名称
                ,count(1)              as city_cnt               --总城市流向线路数量
                ,count(if(search_type <= effective_times_cn ,1,null)) as city_equal_cnt --总城市流向持平线路数量
                ,dt as dt
            from (
                select
                     fast.in_from_city_code     as in_from_city_code    
                    ,fast.in_from_city_desc     as in_from_city_desc   
                    ,fast.out_to_city_code      as out_to_city_code    
                    ,fast.out_to_city_desc      as out_to_city_desc   
                    ,fast.start_area_code       as in_from_agent_code     --始发代理区编码
                    ,fast.start_area_name       as in_from_agent_desc     --始发代理区名称
                    ,fast.start_new_agent_code  as in_from_new_agent_code --新始发代理区编码
                    ,fast.start_new_agent_name  as in_from_new_agent_desc --新始发代理区名称
                    ,fast.start_region_code     as in_from_regional_code  --始发大区编码
                    ,fast.start_region_name     as in_from_regional_desc  --始发大区名称
                    ,fast.end_area_code         as out_to_agent_code      --目的代理区编码
                    ,fast.end_area_name         as out_to_agent_desc      --目的代理区名称
                    ,fast.end_new_agent_code    as out_to_new_agent_code  --新目的代理区编码
                    ,fast.end_new_agent_name    as out_to_new_agent_desc  --新目的代理区名称
                    ,fast.end_region_code       as out_to_regional_code   --目的大区编码
                    ,fast.end_region_name       as out_to_regional_desc   --目的大区名称
                    ,fast.economic_name         as economic_name          --经济圈名称
                    ,min(fast.search_type)      as search_type
                    ,fast.effective_times_cn    as effective_times_cn
                    ,fast.dt as dt
                from dwd_route_city_fast fast 
                group by fast.in_from_city_code
                        ,fast.in_from_city_desc
                        ,fast.out_to_city_code
                        ,fast.out_to_city_desc
                        ,fast.start_area_code
                        ,fast.start_area_name
                        ,fast.start_new_agent_code
                        ,fast.start_new_agent_name
                        ,fast.start_region_code
                        ,fast.start_region_name
                        ,fast.end_area_code
                        ,fast.end_area_name
                        ,fast.end_new_agent_code
                        ,fast.end_new_agent_name
                        ,fast.end_region_code
                        ,fast.end_region_name
                        ,fast.economic_name
                        ,fast.effective_times_cn
                        ,fast.dt
            ) a group by in_from_agent_code     
                        ,in_from_agent_desc     
                        ,in_from_new_agent_code 
                        ,in_from_new_agent_desc 
                        ,in_from_regional_code  
                        ,in_from_regional_desc  
                        ,out_to_agent_code      
                        ,out_to_agent_desc      
                        ,out_to_new_agent_code  
                        ,out_to_new_agent_desc  
                        ,out_to_regional_code   
                        ,out_to_regional_desc   
                        ,economic_name          
                        ,dt
        ) route_city
        union all
        select
             in_from_agent_code      as in_from_agent_code      --始发代理区编码
            ,in_from_agent_desc      as in_from_agent_desc      --始发代理区名称
            ,in_from_new_agent_code  as in_from_new_agent_code  --新始发代理区编码
            ,in_from_new_agent_desc  as in_from_new_agent_desc  --新始发代理区名称
            ,in_from_regional_code   as in_from_regional_code   --始发大区编码
            ,in_from_regional_desc   as in_from_regional_desc   --始发大区名称
            ,out_to_agent_code       as out_to_agent_code       --目的代理区编码
            ,out_to_agent_desc       as out_to_agent_desc       --目的代理区名称
            ,out_to_new_agent_code   as out_to_new_agent_code   --新目的代理区编码
            ,out_to_new_agent_desc   as out_to_new_agent_desc   --新目的代理区名称
            ,out_to_regional_code    as out_to_regional_code    --目的大区编码
            ,out_to_regional_desc    as out_to_regional_desc    --目的大区名称
            ,economic_name           as economic_name           --经济圈名称
            ,0                       as city_cnt                --代理区城市流向线路数量
            ,0                       as city_equal_cnt          --代理区城市流向持平线路数量
            ,sum(equal_line_cnt )    as equal_line_cnt          --持平线路数
            ,sum(slow_line_cnt  )    as slow_line_cnt           --慢于线路数
            ,sum(direct_line_cnt)    as direct_line_cnt         --直发线路数
            ,sum(trans_line_cnt )    as trans_line_cnt          --中转线路数
            ,sum(all_line_cnt   )    as all_line_cnt            --总线路数
            ,sum(equal_cnt      )    as equal_cnt               --持平件量
            ,sum(slow_cnt       )    as slow_cnt                --慢于件量
            ,sum(direct_cnt     )    as direct_cnt              --直发件量
            ,sum(trans_cnt      )    as trans_cnt               --中转件量
            ,sum(all_cnt        )    as all_cnt                 --总件量
            ,dt                      as dt
        from (
             select
                  in_from_city_code        as in_from_city_code       --始发城市编码
                 ,in_from_city_desc        as in_from_city_desc       --始发城市名称
                 ,out_to_city_code         as out_to_city_code        --目的城市编码
                 ,out_to_city_desc         as out_to_city_desc        --目的城市名称
                 ,in_from_agent_code       as in_from_agent_code      --始发代理区编码
                 ,in_from_agent_desc       as in_from_agent_desc      --始发代理区名称
                 ,in_from_new_agent_code   as in_from_new_agent_code  --新始发代理区编码
                 ,in_from_new_agent_desc   as in_from_new_agent_desc  --新始发代理区名称
                 ,in_from_regional_code    as in_from_regional_code   --始发大区编码
                 ,in_from_regional_desc    as in_from_regional_desc   --始发大区名称
                 ,out_to_agent_code        as out_to_agent_code       --目的代理区编码
                 ,out_to_agent_desc        as out_to_agent_desc       --目的代理区名称
                 ,out_to_new_agent_code    as out_to_new_agent_code   --新目的代理区编码
                 ,out_to_new_agent_desc    as out_to_new_agent_desc   --新目的代理区名称
                 ,out_to_regional_code     as out_to_regional_code    --目的大区编码
                 ,out_to_regional_desc     as out_to_regional_desc    --目的大区名称
                 ,economic_name            as economic_name           --经济圈名称
                 ,equal_line_cnt as equal_line_cnt  --持平线路数
                 ,if(slow_line_cnt =  line_cnt,line_cnt       ,0) as slow_line_cnt   --慢于线路数
                 ,if(slow_line_cnt <> line_cnt,direct_line_cnt,0) as direct_line_cnt --直发线路数
                 ,if(slow_line_cnt <> line_cnt,trans_line_cnt ,0) as trans_line_cnt  --中转线路数
                 ,line_cnt  as all_line_cnt   --总线路数
                 ,equal_cnt as equal_cnt      --持平件量
                 ,case when base.line_cnt = 0 then slow_cnt else if(slow_line_cnt = line_cnt,all_cnt  ,0) end as slow_cnt    --慢于件量
                 ,if(slow_line_cnt <> line_cnt,direct_cnt,0) as direct_cnt  --直发件量
                 ,if(slow_line_cnt <> line_cnt,trans_cnt ,0) as trans_cnt   --中转件量
                 ,all_cnt as all_cnt --总件量
                 ,dt      as dt
             from (
                 select
                      in_from_city_code       as in_from_city_code      --始发城市编码
                     ,in_from_city_desc       as in_from_city_desc      --始发城市名称
                     ,out_to_city_code        as out_to_city_code       --目的城市编码
                     ,out_to_city_desc        as out_to_city_desc       --目的城市名称
                     ,start_area_code         as in_from_agent_code     --始发代理区编码
                     ,start_area_name         as in_from_agent_desc     --始发代理区名称
                     ,start_new_agent_code    as in_from_new_agent_code --新始发代理区编码
                     ,start_new_agent_name    as in_from_new_agent_desc --新始发代理区名称
                     ,start_region_code       as in_from_regional_code  --始发大区编码
                     ,start_region_name       as in_from_regional_desc  --始发大区名称
                     ,end_area_code           as out_to_agent_code      --目的代理区编码
                     ,end_area_name           as out_to_agent_desc      --目的代理区名称
                     ,end_new_agent_code      as out_to_new_agent_code  --新目的代理区编码
                     ,end_new_agent_name      as out_to_new_agent_desc  --新目的代理区名称
                     ,end_region_code         as out_to_regional_code   --目的大区编码
                     ,end_region_name         as out_to_regional_desc   --目的大区名称
                     ,economic_name           as economic_name          --经济圈名称
                     ,sum(equal_line_cnt)     as equal_line_cnt         --持平线路数
                     ,sum(slow_line_cnt )     as slow_line_cnt          --慢于线路数
                     ,sum(case when transfer_type in (0,1) then slow_line_cnt else 0 end) as direct_line_cnt --直发线路数
                     ,sum(case when transfer_type not in (0,1) then slow_line_cnt else 0 end) as trans_line_cnt  --中转线路数
                     ,sum(all_line_cnt  ) as line_cnt           --总线路数
                     ,sum(equal_cnt     ) as equal_cnt          --持平件量
                     ,sum(slow_cnt      ) as slow_cnt           --慢于件量
                     ,sum(case when transfer_type in (0,1) then slow_cnt else 0 end) as direct_cnt --直发件量
                     ,sum(case when transfer_type not in (0,1) then slow_cnt else 0 end) as trans_cnt  --中转件量
                     ,sum(all_cnt       ) as all_cnt            --总件量
                     ,dt
                 from dwd_route_city_fast        --有菜鸟时效的数据
                 group by in_from_city_code      --始发城市编码
                         ,in_from_city_desc      --始发城市名称
                         ,out_to_city_code       --目的城市编码
                         ,out_to_city_desc       --目的城市名称
                         ,start_area_code        --始发代理区编码
                         ,start_area_name        --始发代理区名称
                         ,start_new_agent_code   --新始发代理区编码
                         ,start_new_agent_name   --新始发代理区名称
                         ,start_region_code      --始发大区编码
                         ,start_region_name      --始发大区名称
                         ,end_area_code          --目的代理区编码
                         ,end_area_name          --目的代理区名称
                         ,end_new_agent_code     --新目的代理区编码
                         ,end_new_agent_name     --新目的代理区名称
                         ,end_region_code        --目的大区编码
                         ,end_region_name        --目的大区名称
                         ,economic_name          --经济圈名称
                         ,dt
             ) base
        ) a group by in_from_agent_code      --始发代理区编码
                    ,in_from_agent_desc      --始发代理区名称
                    ,in_from_new_agent_code  --新始发代理区编码
                    ,in_from_new_agent_desc  --新始发代理区名称
                    ,in_from_regional_code   --始发大区编码
                    ,in_from_regional_desc   --始发大区名称
                    ,out_to_agent_code       --目的代理区编码
                    ,out_to_agent_desc       --目的代理区名称
                    ,out_to_new_agent_code   --新目的代理区编码
                    ,out_to_new_agent_desc   --新目的代理区名称
                    ,out_to_regional_code    --目的大区编码
                    ,out_to_regional_desc    --目的大区名称
                    ,economic_name           --经济圈名称
                    ,dt
    ) a group by in_from_agent_code      --始发代理区编码
                ,in_from_agent_desc      --始发代理区名称
                ,in_from_new_agent_code  --新始发代理区编码
                ,in_from_new_agent_desc  --新始发代理区名称
                ,in_from_regional_code   --始发大区编码
                ,in_from_regional_desc   --始发大区名称
                ,out_to_agent_code       --目的代理区编码
                ,out_to_agent_desc       --目的代理区名称
                ,out_to_new_agent_code   --新目的代理区编码
                ,out_to_new_agent_desc   --新目的代理区名称
                ,out_to_regional_code    --目的大区编码
                ,out_to_regional_desc    --目的大区名称
                ,economic_name           --经济圈名称
                ,dt
) a;






insert overwrite table jms_dm.dm_route_city_summary_dt
select
     in_from_regional_code    --始发大区编码
    ,in_from_regional_desc    --始发大区名称
    ,out_to_regional_code     --目的大区编码
    ,out_to_regional_desc     --目的大区名称
    ,in_from_agent_code       --始发代理区编码
    ,in_from_agent_desc       --始发代理区名称
    ,out_to_agent_code        --目的代理区编码
    ,out_to_agent_desc        --目的代理区名称
    ,city_cnt                 --代理区城市流向线路数量
    ,city_equal_cnt           --代理区城市流向持平线路数量
    ,equal_cnt                --持平件量
    ,slow_cnt                 --慢于件量
    ,direct_cnt               --直发件量
    ,trans_cnt                --中转件量
    ,all_cnt                  --总件量
    ,equal_line_cnt           --持平线路数
    ,slow_line_cnt            --慢于线路数
    ,direct_line_cnt          --直发线路数
    ,trans_line_cnt           --中转线路数
    ,all_line_cnt             --总线路数
    ,all_city_cnt             --全国流向 
    ,all_city_equal_cnt       --全国持平流向
    ,all_equal_piect_cnt      --全国持平件量
    ,all_slow_piect_cnt       --全国慢于件量
    ,all_direct_piect_cnt     --全国直发件量
    ,all_trans_piect_cnt      --全国中转件量
    ,all_piect_cnt            --全国件量
    ,all_equal_line_cnt       --全国持平线路数
    ,all_slow_line_cnt        --全国慢于线路数
    ,all_direct_line_cnt      --全国直发线路数
    ,all_trans_line_cnt       --全国中转线路数
    ,all_any_line_cnt         --全国总线路数
    ,dt                       --分区日期
from jms_tmp.dm_route_city_summary_dt_tmp
where dt = '{{ execution_date | cst_ds }}'
union all
select
     in_from_regional_code        as in_from_regional_code     --始发大区编码
    ,in_from_regional_desc        as in_from_regional_desc     --始发大区名称
    ,null                         as out_to_regional_code      --目的大区编码
    ,null                         as out_to_regional_desc      --目的大区名称
    ,in_from_new_agent_code       as in_from_agent_code        --新始发代理区编码
    ,in_from_new_agent_desc       as in_from_agent_desc        --新始发代理区名称
    ,null                         as out_to_agent_code         --新目的代理区编码
    ,null                         as out_to_agent_desc         --新目的代理区名称
    ,sum(city_cnt           )     as city_cnt                  --代理区城市流向线路数量
    ,sum(city_equal_cnt     )     as city_equal_cnt            --代理区城市流向持平线路数量
    ,sum(equal_cnt          )     as equal_cnt                 --持平件量
    ,sum(slow_cnt           )     as slow_cnt                  --慢于件量
    ,sum(direct_cnt         )     as direct_cnt                --直发件量
    ,sum(trans_cnt          )     as trans_cnt                 --中转件量
    ,sum(all_cnt            )     as all_cnt                   --总件量
    ,sum(equal_line_cnt     )     as equal_line_cnt            --持平线路数
    ,sum(slow_line_cnt      )     as slow_line_cnt             --慢于线路数
    ,sum(direct_line_cnt    )     as direct_line_cnt           --直发线路数
    ,sum(trans_line_cnt     )     as trans_line_cnt            --中转线路数
    ,sum(all_line_cnt       )     as all_line_cnt              --总线路数
    ,max(all_city_cnt       )     as all_city_cnt              --全国流向
    ,max(all_city_equal_cnt )     as all_city_equal_cnt        --全国持平流向
    ,max(all_equal_piect_cnt )    as all_equal_piect_cnt       --全国持平件量
    ,max(all_slow_piect_cnt  )    as all_slow_piect_cnt        --全国慢于件量
    ,max(all_direct_piect_cnt)    as all_direct_piect_cnt      --全国直发件量
    ,max(all_trans_piect_cnt )    as all_trans_piect_cnt       --全国中转件量
    ,max(all_piect_cnt       )    as all_piect_cnt             --全国件量
    ,max(all_equal_line_cnt  )    as all_equal_line_cnt        --全国持平线路数
    ,max(all_slow_line_cnt   )    as all_slow_line_cnt         --全国慢于线路数
    ,max(all_direct_line_cnt )    as all_direct_line_cnt       --全国直发线路数
    ,max(all_trans_line_cnt  )    as all_trans_line_cnt        --全国中转线路数
    ,max(all_any_line_cnt    )    as all_any_line_cnt          --全国总线路数
    ,dt
from jms_tmp.dm_route_city_summary_dt_tmp
where in_from_new_agent_desc in ('广深','浙江','江苏','京津冀','东北','渝贵','甘青宁')
and dt = '{{ execution_date | cst_ds }}'
group by in_from_regional_code
        ,in_from_regional_desc
        ,in_from_new_agent_code
        ,in_from_new_agent_desc
        ,dt
union all
select
     null                         as in_from_regional_code     --始发大区编码
    ,null                         as in_from_regional_desc     --始发大区名称
    ,out_to_regional_code         as out_to_regional_code      --目的大区编码
    ,out_to_regional_desc         as out_to_regional_desc      --目的大区名称
    ,null                         as in_from_agent_code        --新始发代理区编码
    ,null                         as in_from_agent_desc        --新始发代理区名称
    ,out_to_new_agent_code        as out_to_agent_code         --新目的代理区编码
    ,out_to_new_agent_desc        as out_to_agent_desc         --新目的代理区名称
    ,sum(city_cnt           )     as city_cnt                  --代理区城市流向线路数量
    ,sum(city_equal_cnt     )     as city_equal_cnt            --代理区城市流向持平线路数量
    ,sum(equal_cnt          )     as equal_cnt                 --持平件量
    ,sum(slow_cnt           )     as slow_cnt                  --慢于件量
    ,sum(direct_cnt         )     as direct_cnt                --直发件量
    ,sum(trans_cnt          )     as trans_cnt                 --中转件量
    ,sum(all_cnt            )     as all_cnt                   --总件量
    ,sum(equal_line_cnt     )     as equal_line_cnt            --持平线路数
    ,sum(slow_line_cnt      )     as slow_line_cnt             --慢于线路数
    ,sum(direct_line_cnt    )     as direct_line_cnt           --直发线路数
    ,sum(trans_line_cnt     )     as trans_line_cnt            --中转线路数
    ,sum(all_line_cnt       )     as all_line_cnt              --总线路数
    ,max(all_city_cnt       )     as all_city_cnt              --全国流向
    ,max(all_city_equal_cnt )     as all_city_equal_cnt        --全国持平流向
    ,max(all_equal_piect_cnt )    as all_equal_piect_cnt       --全国持平件量
    ,max(all_slow_piect_cnt  )    as all_slow_piect_cnt        --全国慢于件量
    ,max(all_direct_piect_cnt)    as all_direct_piect_cnt      --全国直发件量
    ,max(all_trans_piect_cnt )    as all_trans_piect_cnt       --全国中转件量
    ,max(all_piect_cnt       )    as all_piect_cnt             --全国件量
    ,max(all_equal_line_cnt  )    as all_equal_line_cnt        --全国持平线路数
    ,max(all_slow_line_cnt   )    as all_slow_line_cnt         --全国慢于线路数
    ,max(all_direct_line_cnt )    as all_direct_line_cnt       --全国直发线路数
    ,max(all_trans_line_cnt  )    as all_trans_line_cnt        --全国中转线路数
    ,max(all_any_line_cnt    )    as all_any_line_cnt          --全国总线路数
    ,dt
from jms_tmp.dm_route_city_summary_dt_tmp
where out_to_new_agent_desc in ('广深','浙江','江苏','京津冀','东北','渝贵','甘青宁')
and dt = '{{ execution_date | cst_ds }}'
group by out_to_regional_code
        ,out_to_regional_desc
        ,out_to_new_agent_code
        ,out_to_new_agent_desc
        ,dt
union all 
select
     1                            as in_from_regional_code     --始发大区编码
    ,'经济圈'                     as in_from_regional_desc     --始发大区名称
    ,1                            as out_to_regional_code      --目的大区编码
    ,'经济圈'                     as out_to_regional_desc      --目的大区名称
    ,case when economic_name = '长三角' then 1
          when economic_name = '珠三角' then 2
          when economic_name = '京津冀' then 3
          when economic_name = '东北'   then 4
          end                     as in_from_agent_code    --新始发代理区编码
    ,economic_name                as in_from_agent_desc    --新始发代理区名称
    ,case when economic_name = '长三角' then 1
          when economic_name = '珠三角' then 2
          when economic_name = '京津冀' then 3
          when economic_name = '东北'   then 4
          end                     as out_to_agent_code     --新目的代理区编码
    ,economic_name                as out_to_agent_desc     --新目的代理区名称
    ,sum(city_cnt           )     as city_cnt                  --代理区城市流向线路数量
    ,sum(city_equal_cnt     )     as city_equal_cnt            --代理区城市流向持平线路数量
    ,sum(equal_cnt          )     as equal_cnt                 --持平件量
    ,sum(slow_cnt           )     as slow_cnt                  --慢于件量
    ,sum(direct_cnt         )     as direct_cnt                --直发件量
    ,sum(trans_cnt          )     as trans_cnt                 --中转件量
    ,sum(all_cnt            )     as all_cnt                   --总件量
    ,sum(equal_line_cnt     )     as equal_line_cnt            --持平线路数
    ,sum(slow_line_cnt      )     as slow_line_cnt             --慢于线路数
    ,sum(direct_line_cnt    )     as direct_line_cnt           --直发线路数
    ,sum(trans_line_cnt     )     as trans_line_cnt            --中转线路数
    ,sum(all_line_cnt       )     as all_line_cnt              --总线路数
    ,max(all_city_cnt       )     as all_city_cnt              --全国流向
    ,max(all_city_equal_cnt )     as all_city_equal_cnt        --全国持平流向
    ,max(all_equal_piect_cnt )    as all_equal_piect_cnt       --全国持平件量
    ,max(all_slow_piect_cnt  )    as all_slow_piect_cnt        --全国慢于件量
    ,max(all_direct_piect_cnt)    as all_direct_piect_cnt      --全国直发件量
    ,max(all_trans_piect_cnt )    as all_trans_piect_cnt       --全国中转件量
    ,max(all_piect_cnt       )    as all_piect_cnt             --全国件量
    ,max(all_equal_line_cnt  )    as all_equal_line_cnt        --全国持平线路数
    ,max(all_slow_line_cnt   )    as all_slow_line_cnt         --全国慢于线路数
    ,max(all_direct_line_cnt )    as all_direct_line_cnt       --全国直发线路数
    ,max(all_trans_line_cnt  )    as all_trans_line_cnt        --全国中转线路数
    ,max(all_any_line_cnt    )    as all_any_line_cnt          --全国总线路数
    ,dt                           as dt                        --分区日期
from jms_tmp.dm_route_city_summary_dt_tmp
where economic_name is not null
and dt = '{{ execution_date | cst_ds }}'
group by economic_name
        ,dt
distribute by pmod(hash(rand()),2)
;

